 
GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableDeleteSingleItem]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableDeleteSingleItem]
(
	@EmployeeTimeSheetKey bigint
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	DELETE
	FROM [EmployeeTimeSheetTable]
	WHERE
		[EmployeeTimeSheetKey] = @EmployeeTimeSheetKey
	SET @Err = @@Error

	RETURN @Err
END


GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableInsertUpdateSingleItem]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableInsertUpdateSingleItem]
(
	@EmployeeTimeSheetKey bigint,
	@EmployeeProfileKey int,
	@UserAccountKey int,
	@DayCode smallint,
	@TimeIn datetime,
	@TimeOut datetime,
	@Remarks nvarchar(400) = NULL,
	@TimeSheetStatus bit,
	@Status smallint = NULL,
	@CreatedDateTime datetime = NULL,
	@ModifiedDateTime datetime = NULL
)
AS
DECLARE @ReturnValue int
IF (@EmployeeTimeSheetKey IS NULL) -- New Item
BEGIN

	INSERT
	INTO [EmployeeTimeSheetTable]
	(
		[EmployeeProfileKey],
		[UserAccountKey],
		[DayCode],
		[TimeIn],
		[TimeOut],
		[Remarks],
		[TimeSheetStatus],
		[Status],
		[CreatedDateTime],
		[ModifiedDateTime]
	)
		VALUES
		(
			@EmployeeProfileKey,
		@UserAccountKey,
		@DayCode,
		@TimeIn,
		@TimeOut,
		@Remarks,
		@TimeSheetStatus,
		@Status,
		@CreatedDateTime,
		@ModifiedDateTime
	)
	
	SELECT @ReturnValue = SCOPE_IDENTITY()

	END
	ELSE
	BEGIN

	UPDATE [EmployeeTimeSheetTable]
	SET
		[EmployeeProfileKey] = @EmployeeProfileKey,
		[UserAccountKey] = @UserAccountKey,
		[DayCode] = @DayCode,
		[TimeIn] = @TimeIn,
		[TimeOut] = @TimeOut,
		[Remarks] = @Remarks,
		[TimeSheetStatus] = @TimeSheetStatus,
		[Status] = @Status,
		[CreatedDateTime] = @CreatedDateTime,
		[ModifiedDateTime] = @ModifiedDateTime
	WHERE
		[EmployeeTimeSheetKey] = @EmployeeTimeSheetKey

SELECT @ReturnValue = @EmployeeTimeSheetKey

END
  IF (@@ERROR != 0)
  BEGIN
    RETURN -1
  END
  ELSE
  BEGIN
    RETURN @ReturnValue
  END



GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableSelectList]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableSelectList]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		EAT.[EmployeeTimeSheetKey],
		EAT.[EmployeeProfileKey],
		EAT.[UserAccountKey],
		EAT.[DayCode],
		EAT.[TimeIn],
		EAT.[TimeOut],
		EAT.[Remarks],
		EAT.[TimeSheetStatus],
		EAT.[Status],
		EAT.[CreatedDateTime],
		EAT.[ModifiedDateTime]
		 
	FROM [EmployeeTimeSheetTable] EAT
     
	SET @Err = @@Error

	RETURN @Err
END


GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableSelectListByEmployeeProfileKey]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableSelectListByEmployeeProfileKey]
	@EmployeeProfileKey int

AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[EmployeeTimeSheetKey],
		[EmployeeProfileKey],
		[UserAccountKey],
		[DayCode],
		[TimeIn],
		[TimeOut],
		[Remarks],
		[TimeSheetStatus],
		[Status],
		[CreatedDateTime],
		[ModifiedDateTime]
	FROM [EmployeeTimeSheetTable]
	
	WHERE 		([EmployeeProfileKey] = @EmployeeProfileKey)

	SET @Err = @@Error

	RETURN @Err
END


GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableSelectListBySchoolKeyNEmployee]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableSelectListBySchoolKeyNEmployee]
@SchoolKey int ,
@EmployeeProfileKey varchar(20),
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		EAT.[EmployeeTimeSheetKey],
		EAT.[EmployeeProfileKey],
		EAT.[UserAccountKey],
		EAT.[DayCode],
		EAT.[TimeIn],
		EAT.[TimeOut],
		EAT.[Remarks],
		EAT.[TimeSheetStatus],
		EAT.[Status],
		EAT.[CreatedDateTime],
		EAT.[ModifiedDateTime],
		EP.FirstName + ' ' + EP.LastName AS EmployeeName,
		UAC.SchoolKey,
		DC.Description AS 'DayCodeDesc'
	FROM [EmployeeTimeSheetTable] EAT
    INNER JOIN EmployeeProfileTable EP  ON  EP.EmployeeProfileKey = EAT.EmployeeProfileKey
    INNER JOIN UserAccountTable UAC  ON UAC.UserAccountKey = EAT.UserAccountKey
    INNER JOIN DayCodeTable DC ON DC.DayCode = EAT.DayCode
    WHERE UAC.SchoolKey LIKE @SchoolKey AND EAT.EmployeeProfileKey LIKE  @EmployeeProfileKey
    AND  (Year(EAT.TimeIn)= YEAR(@StartDate)AND MONTH(EAT.TimeIn)= MONTH(@StartDate) AND DAY(EAT.TimeIn) = DAY(@StartDate) )
    AND (Year(EAT.TimeOut)= YEAR(@EndDate)AND MONTH(EAT.TimeOut)= MONTH(@EndDate) AND DAY(EAT.TimeOut) = DAY(@EndDate) )    
    ORDER BY EAT.[EmployeeTimeSheetKey]
    
    SET @Err = @@Error

	RETURN @Err
END


 


GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableSelectListBySchoolKeyNUserAccount]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableSelectListBySchoolKeyNUserAccount]
@SchoolKey int ,
@UserAccountKey varchar(20),
@StartDate DateTime,
@EndDate DateTime
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		EAT.[EmployeeTimeSheetKey],
		EAT.[EmployeeProfileKey],
		EAT.[UserAccountKey],
		EAT.[DayCode],
		EAT.[TimeIn],
		EAT.[TimeOut],
		EAT.[Remarks],
		EAT.[TimeSheetStatus],
		EAT.[Status],
		EAT.[CreatedDateTime],
		EAT.[ModifiedDateTime],
		EP.FirstName + ' ' + EP.LastName AS EmployeeName,
		UAC.SchoolKey,
		DC.Description AS 'DayCodeDesc'
	FROM [EmployeeTimeSheetTable] EAT
    INNER JOIN EmployeeProfileTable EP  ON  EP.EmployeeProfileKey = EAT.EmployeeProfileKey
    INNER JOIN UserAccountTable UAC  ON UAC.UserAccountKey = EAT.UserAccountKey
    INNER JOIN DayCodeTable DC ON DC.DayCode = EAT.DayCode
    WHERE UAC.SchoolKey = @SchoolKey AND EAT.UserAccountKey = @UserAccountKey
    AND  (Year(EAT.TimeIn)= YEAR(@StartDate)AND MONTH(EAT.TimeIn)= MONTH(@StartDate) AND DAY(EAT.TimeIn) = DAY(@StartDate) )
    AND (Year(EAT.TimeOut)= YEAR(@EndDate)AND MONTH(EAT.TimeOut)= MONTH(@EndDate) AND DAY(EAT.TimeOut) = DAY(@EndDate) )    
    ORDER BY EAT.[EmployeeTimeSheetKey]
    
    SET @Err = @@Error

	RETURN @Err
END
GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableSelectListByUserAccountKey]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableSelectListByUserAccountKey]
	@UserAccountKey int

AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[EmployeeTimeSheetKey],
		[EmployeeProfileKey],
		[UserAccountKey],
		[DayCode],
		[TimeIn],
		[TimeOut],
		[Remarks],
		[TimeSheetStatus],
		[Status],
		[CreatedDateTime],
		[ModifiedDateTime]
	FROM [EmployeeTimeSheetTable]
	
	WHERE 		([UserAccountKey] = @UserAccountKey)

	SET @Err = @@Error

	RETURN @Err
END


GO

/****** Object:  StoredProcedure [dbo].[sprocEmployeeTimeSheetTableSelectSingleItem]    Script Date: 07/05/2012 17:08:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sprocEmployeeTimeSheetTableSelectSingleItem]
(
	@EmployeeTimeSheetKey bigint
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		EAT.[EmployeeTimeSheetKey],
		EAT.[EmployeeProfileKey],
		EAT.[UserAccountKey],
		EAT.[DayCode],
		EAT.[TimeIn],
		EAT.[TimeOut],
		EAT.[Remarks],
		EAT.[TimeSheetStatus],
		EAT.[Status],
		EAT.[CreatedDateTime],
		EAT.[ModifiedDateTime],
		EP.FirstName + ' ' + EP.LastName AS EmployeeName,
		UAC.SchoolKey,
		DC.Description AS 'DayCodeDesc' 
	FROM [EmployeeTimeSheetTable] EAT
    INNER JOIN EmployeeProfileTable EP  ON  EP.EmployeeProfileKey = EAT.EmployeeProfileKey
    INNER JOIN UserAccountTable UAC  ON UAC.UserAccountKey = EAT.UserAccountKey   
    INNER JOIN DayCodeTable DC ON DC.DayCode  = EAT.DayCode 
    WHERE ([EmployeeTimeSheetKey] = @EmployeeTimeSheetKey)  
    ORDER BY EAT.[EmployeeTimeSheetKey]	

	SET @Err = @@Error

	RETURN @Err
END


GO


